﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using System.Text;
using System.Data.SqlClient;
using System.Data;

[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class AjaxDataViewService
{
    // To use HTTP GET, add [WebGet] attribute. (Default ResponseFormat is WebMessageFormat.Json)
    // To create an operation that returns XML,
    //     add [WebGet(ResponseFormat=WebMessageFormat.Xml)],
    //     and include the following line in the operation body:
    //         WebOperationContext.Current.OutgoingResponse.ContentType = "text/xml";
    [OperationContract]
    public void DoWork()
    {
        // Add your operation implementation here
        return;
    }

    // Add more operations here and mark them with [OperationContract]


    [OperationContract]
    public List<CustomerInfo> GetUsers(string CustomerIDPattern)
    {
        List<CustomerInfo> result = new List<CustomerInfo>();
        using (SqlConnection con = new SqlConnection("Data Source=PETERZHU\\PETERSQL2008;Initial Catalog=Northwind;Integrated Security=True;"))
        {
            con.Open();
            var cmd = con.CreateCommand();
            if (!string.IsNullOrWhiteSpace(CustomerIDPattern))
            {
                //cmd.CommandText = "select CustomerID,CompanyName,ContactName from Customers  where" + " CustomerID like '%@CustomerIDPattern%'";
                //cmd.Parameters.Add("@CustomerIDPattern", SqlDbType.NVarChar, 15).Value = CustomerIDPattern;

                cmd.CommandText = "select CustomerID,CompanyName,ContactName from Customers  where" + " CustomerID like '%" + CustomerIDPattern + "%'";

            }
            else
            {
                cmd.CommandText = "select CustomerID,CompanyName,ContactName from Customers";
            }
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                result.Add(new CustomerInfo()
                {
                    CustomerID = reader["CustomerID"].ToString(),
                    CompanyName = reader["CompanyName"].ToString(),
                    ContactName = reader["ContactName"].ToString()
                });
            }
            reader.Close();
        }
        return result;
    }

}


[DataContract]
public class CustomerInfo
{
    [DataMember]
    public string CustomerID { get; set; }
    [DataMember]
    public string CompanyName { get; set; }
    [DataMember]
    public string ContactName { get; set; }
}